package com.ms.biz.init;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.system.comm.utils.FrameStringUtil;
import com.system.dao.BaseDao;
import com.system.dao.utils.DbUtil;
import com.system.ds.DbContextHolder;

/**
 * 脚本升级
 * @author yuejing
 * @date 2018年12月18日 上午11:17:25
 */
public class DbUpdate {

	private static final Logger LOGGER = LoggerFactory.getLogger(DbUpdate.class);

	/**
	 * 初始化更新后的脚本
	 */
	public static void init() {
		/*if (EnvUtil.projectDbUpdate() != 1) {
			return;
		}*/
		/*ThreadPoolTaskExecutor task = FrameSpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
		task.execute(new Runnable() {

			@Override
			public void run() {*/
		version1_0_0();
		/*}

		});*/
	}
	
	/*private static void version2_0_1(BaseDao baseDao) {
	String sql = null;
	// 任务表增加字段: 失败发送邮件规则
	List<TableField> fields = baseDao.tableFields("task_job");
	boolean isFailemailrule = false;
	for (TableField f : fields) {
		if ("failemailrule".equalsIgnoreCase(f.getName())) {
			isFailemailrule = true;
		}
	}
	if (!isFailemailrule) {
		LOGGER.info("|===================================================");
		LOGGER.info("|========== 表[task_job]增加字段: failemailrule");
		sql = "alter table task_job add failemailrule varchar(250) comment '失败发送邮件规则'";
		baseDao.updateSql(sql);
		LOGGER.info("|===================================================");
	}

}*/
	private static void version1_0_0() {
		DbContextHolder.setDsName("dataSource1");
		BaseDao baseDao = new BaseDao();
		if (DbUtil.isMysql()) {
			createFileInfoMysql(baseDao);
			createSysDictTypeMysql(baseDao);
			createSysDictMysql(baseDao);
		} else if (DbUtil.isOracle()) {
			createSysDictTypeOracle(baseDao);
			createSysDictOracle(baseDao);
		}
	}

	private static void createFileInfoMysql(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from file_info limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[file_info]: " + e.getMessage());
			String sql = "create table file_info\r\n" + 
					"(\r\n" + 
					"   file_id              varchar(32) not null comment '编号',\r\n" + 
					"   sys_name             varchar(150) not null comment '系统名称',\r\n" + 
					"   org_name             varchar(150) not null comment '原名称',\r\n" + 
					"   system               varchar(100) not null comment '来源系统',\r\n" + 
					"   type                 varchar(100) not null comment '分类',\r\n" + 
					"   save_path            varchar(250) not null comment '保存路径',\r\n" + 
					"   browse_url           varchar(250) not null comment '访问路径',\r\n" + 
					"   file_size            float(20,2) not null comment '文件大小',\r\n" + 
					"   upload_time          datetime not null comment '上传时间',\r\n" + 
					"   primary key (file_id)\r\n" + 
					");\r\n" + 
					"\r\n" + 
					"alter table file_info comment '文件表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}

	private static void createSysDictTypeMysql(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_dict_type limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_dict_type]: " + e.getMessage());
			String sql = "CREATE TABLE sys_dict_type (\r\n" + 
					"				code varchar(32) NOT NULL COMMENT '编码',\r\n" + 
					"				name varchar(100) NOT NULL COMMENT '名称',\r\n" + 
					"			  	parent_code varchar(32) default '0' COMMENT '上级编码',\r\n" + 
					"				remark varchar(256) default NULL COMMENT '备注',\r\n" + 
					"				create_user_id varchar(32) NOT NULL COMMENT '创建人',\r\n" + 
					"				create_time datetime NOT NULL COMMENT '创建时间',\r\n" + 
					"			  	PRIMARY KEY (code)\r\n" + 
					"			) COMMENT='系统字典分类表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSysDictMysql(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_dict limit 1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_dict]: " + e.getMessage());
			String sql = "CREATE TABLE sys_dict (\r\n" + 
					"			  	dict_id varchar(32) NOT NULL COMMENT '编号',\r\n" + 
					"			  	type_code varchar(32) NOT NULL COMMENT '类型编码',\r\n" + 
					"			  	name varchar(100) NOT NULL COMMENT '名称',\r\n" + 
					"			  	value varchar(256) NOT NULL COMMENT '值',\r\n" + 
					"			  	remark varchar(256) default NULL COMMENT '备注',\r\n" + 
					"			  	create_user_id varchar(32) NOT NULL COMMENT '创建人',\r\n" + 
					"			  	create_time datetime NOT NULL COMMENT '创建时间',\r\n" + 
					"			  	PRIMARY KEY  (dict_id, type_code)\r\n" + 
					"			) COMMENT='系统字典值表';";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSysDictTypeOracle(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_dict_type where rownum=1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_dict_type]: " + e.getMessage());
			String sql = "create table sys_dict_type \r\n" + 
					"			(\r\n" + 
					"			   code               VARCHAR2(32)         not null,\r\n" + 
					"			   name               VARCHAR2(100)        not null,\r\n" + 
					"			   remark             VARCHAR2(200),\r\n" + 
					"			   parent_code        VARCHAR2(32)         not null,\r\n" + 
					"			   create_user_id        VARCHAR2(32)         not null,\r\n" + 
					"			   create_time        DATE                 not null,\r\n" + 
					"			   constraint PK_SYS_DICT_TYPE primary key (code)\r\n" + 
					"			)';\r\n" + 
					"			comment on table sys_dict_type is '系统字典类型表';\r\n" + 
					"			comment on column sys_dict_type.code is '编号';\r\n" + 
					"			comment on column sys_dict_type.name is '类型名称';\r\n" + 
					"			comment on column sys_dict_type.remark is '备注';\r\n" + 
					"			comment on column sys_dict_type.parent_code is '父级编码';\r\n" + 
					"			comment on column sys_dict_type.create_user_id is '添加人';\r\n" + 
					"			comment on column sys_dict_type.create_time is '添加时间';\r\n";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	private static void createSysDictOracle(BaseDao baseDao) {
		try {
			baseDao.queryForLong("select count(*) from sys_dict where rownum=1");
		} catch (Exception e) {
			LOGGER.info("|===================================================");
			LOGGER.error("|========== 不存在表[sys_dict]: " + e.getMessage());
			String sql = "create table sys_dict \r\n" + 
					"			(\r\n" + 
					"			   dict_id            VARCHAR2(32)         not null,\r\n" + 
					"			   type_code          VARCHAR2(32)         not null,\r\n" + 
					"			   name               VARCHAR2(50)         not null,\r\n" + 
					"			   value              VARCHAR2(200)        not null,\r\n" + 
					"			   remark             VARCHAR2(200),\r\n" + 
					"			   create_user_id        VARCHAR2(32)         not null,\r\n" + 
					"			   create_time        DATE                 not null,\r\n" + 
					"			   constraint PK_SYS_DICT primary key (dict_id,type_code)\r\n" + 
					"			);\r\n" + 
					"			comment on table sys_dict is '系统字典值表';\r\n" + 
					"			comment on column sys_dict.dict_id is '编号';\r\n" + 
					"			comment on column sys_dict.type_code is '类型编码';\r\n" + 
					"			comment on column sys_dict.name is '名称';\r\n" + 
					"			comment on column sys_dict.value is '值';\r\n" + 
					"			comment on column sys_dict.remark is '备注';\r\n" + 
					"			comment on column sys_dict.create_user_id is '添加人';\r\n" + 
					"			comment on column sys_dict.create_time is '添加时间';\r\n";
			batchExec(sql, baseDao);
			LOGGER.info("|===================================================");
		}
	}
	
	private static void batchExec(String sql, BaseDao baseDao) {
		List<String> sqls = FrameStringUtil.toArray(sql, ";");
		for (String s : sqls) {
			if (FrameStringUtil.isEmpty(s)) {
				continue;
			}
			baseDao.updateSql(s);
		}
	}
}
